package db_java;

import static java.lang.String.format;
import static org.junit.Assert.assertEquals;

import java.sql.Connection;

import org.junit.After;
import org.junit.Before;
import org.junit.Rule;
import org.junit.Test;
import org.junit.contrib.java.lang.system.StandardOutputStreamLog;

import com.basicorm.db.DbTestUtil;



public class DBConnectTest {
    private static final String TABLE_NAME_COMPANY = "company";
    private static final String SEQUENCE_FOR_COMPANY = "company_id_seq";
    private static final String TABLE_NAME_CLIENT = "clients";
    private static final String TABLE_NAME_DUAL = "dual";


    private Connection connection;
    private DbTestUtil dbTestUtil;
    
    @Rule
    public StandardOutputStreamLog outData = new StandardOutputStreamLog();

	
    @Before
    public void setUp() throws Exception {
        connection = DbTestUtil.getConnection();
        dbTestUtil = new DbTestUtil(connection);
        dbTestUtil.createTable("create sequence company_id_seq start with 1");
        dbTestUtil.createTable("create table company (company_id int PRIMARY KEY, company_name varchar(10), company_add varchar(10))");
        dbTestUtil.createTable("CREATE TRIGGER trigg BEFORE INSERT ON company REFERENCING NEW ROW AS newrow FOR EACH ROW SET newrow.company_id = NEXT VALUE FOR company_id_seq");
        dbTestUtil.createTable("create table clients (client_fn varchar(30), client_ln varchar(30), company_id int)");
        dbTestUtil.createTable("alter table clients add foreign key (company_id) references company (company_id)");
        dbTestUtil.createTable("create table dual (DUMMY varchar(2))");
    }
    
    
    @Test
	public void testCommandExecutePutCompany() throws Exception {
    	String[] args = {"put", "company", "company=P&G", "companyAdd=CA"};
    	DBConnect.commandExecute(connection, args);
    	assertEquals(1, dbTestUtil.countFromTable(TABLE_NAME_COMPANY));
    	assertEquals(format("Company name: P&G; Company address: CA%n"), outData.getLog());
	}
    
    @Test
	public void testCommandExecuteGetCompany() throws Exception {
    	connection.createStatement().executeUpdate("insert into " + TABLE_NAME_COMPANY + "(company_name, company_add) values('P&G', 'CA')");
    	String[] args = {"get", "company", "company=P&G"};
    	DBConnect.commandExecute(connection, args);
    	assertEquals(1, dbTestUtil.countFromTable(TABLE_NAME_COMPANY));
    	assertEquals(format("Company name: P&G; Company address: CA%n"), outData.getLog());
	}    
    
    @Test
	public void testCommandExecuteListCompany() throws Exception {
    	connection.createStatement().executeUpdate("insert into " + TABLE_NAME_COMPANY + "(company_name, company_add) values('P&G', 'CA')");
    	String[] args = {"list", "company"};
    	DBConnect.commandExecute(connection, args);
    	assertEquals(1, dbTestUtil.countFromTable(TABLE_NAME_COMPANY));
    	assertEquals(format("Company name: P&G; Company address: CA%n"), outData.getLog());
	} 
    
    @Test
	public void testCommandExecutePutClient() throws Exception {
    	connection.createStatement().executeUpdate("insert into " + TABLE_NAME_COMPANY + "(company_name, company_add) values('P&G', 'CA')");
    	String[] args = {"put", "client", "company=P&G", "firstName=Sahra", "lastName=Smith"};
    	DBConnect.commandExecute(connection, args);
    	assertEquals(1, dbTestUtil.countFromTable(TABLE_NAME_CLIENT));
    	assertEquals(format("First name: Sahra; Last name: Smith; Company name: P&G%n"), outData.getLog());
	}
    
    @Test
	public void testCommandExecuteGetClient() throws Exception {
    	connection.createStatement().executeUpdate("insert into " + TABLE_NAME_COMPANY + "(company_name, company_add) values('P&G', 'CA')");
    	connection.createStatement().executeUpdate("insert into " + TABLE_NAME_CLIENT + "(client_fn, client_ln, company_id) values('Sahra', 'Smith', (select company_id from company where company_name = 'P&G'))");
    	String[] args = {"get", "client", "company='P&G'"};
    	DBConnect.commandExecute(connection, args);
    	assertEquals(1, dbTestUtil.countFromTable(TABLE_NAME_CLIENT));
    	assertEquals(format("First name: Sahra; Last name: Smith; Company name: P&G%n"), outData.getLog());
	} 
    
    @Test
	public void testCommandExecuteListClient() throws Exception {
    	connection.createStatement().executeUpdate("insert into " + TABLE_NAME_COMPANY + "(company_name, company_add) values('P&G', 'CA')");
    	connection.createStatement().executeUpdate("insert into " + TABLE_NAME_CLIENT + "(client_fn, client_ln, company_id) values('Sahra', 'Smith', (select company_id from company where company_name = 'P&G'))");
    	String[] args = {"list", "client"};
    	DBConnect.commandExecute(connection, args);
    	assertEquals(1, dbTestUtil.countFromTable(TABLE_NAME_CLIENT));
    	assertEquals(format("First name: Sahra; Last name: Smith; Company name: P&G%n"), outData.getLog());
	}
    
    
	@After 
    public void tearDown() throws Exception {
    	dbTestUtil.dropTable(TABLE_NAME_CLIENT);
    	dbTestUtil.dropTable(TABLE_NAME_COMPANY);
    	dbTestUtil.dropTable(TABLE_NAME_DUAL);
    	dbTestUtil.dropSequence(SEQUENCE_FOR_COMPANY);
        connection.close();
    }

}
